CREATE TABLE [dbo].[Article] (
	[ArticleID] [int] IDENTITY (1000, 1) NOT NULL ,
	[PostID] [int] NOT NULL ,
	[Topic] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[SubTopic] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[BibleTitle] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[BibleChapter] [smallint] NULL ,
	[BibleSection] [smallint] NULL ,
	[BibleChapter_To] [smallint] NULL ,
	[BibleSection_To] [smallint] NULL ,
	[Vote_yes] [smallint] NULL ,
	[Vote_no] [smallint] NULL ,
	[ApproveOpinion] [nvarchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ApproveUserID] [int] NULL ,
	[ApproveTime] [datetime] NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Article] ADD 
	CONSTRAINT [PK_Article] PRIMARY KEY  CLUSTERED 
	(
		[ArticleID]
	)  ON [PRIMARY] 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE VIEW dbo.v_Articles
AS
SELECT TOP 100 PERCENT dbo.ForumPosts.PostID, dbo.ForumPosts.CategoryID, 
      dbo.ForumPosts.ParentPostID, dbo.ForumPosts.Subject, dbo.ForumPosts.Body, 
      dbo.ForumPosts.CreateDate, dbo.ForumPosts.Status, dbo.ForumPosts.Summary, 
      dbo.ForumPosts.Type, dbo.ForumPosts.UserID, dbo.ForumPosts.NumberOfVisits, 
      dbo.ForumPosts.PartnerID, dbo.ForumPosts.OriginAuthor, dbo.ForumPosts.URL, 
      dbo.ForumPosts.OriginalDate, dbo.ForumPosts.LastModifiedBy, 
      dbo.ForumPosts.LastModifiedDateTime, dbo.ForumPosts.CreateUser, 
      dbo.Article.ArticleID, dbo.Article.Topic, dbo.Article.SubTopic, dbo.Article.BibleTitle, 
      dbo.Article.BibleChapter, dbo.Article.BibleSection, dbo.Article.BibleChapter_To, 
      dbo.Article.BibleSection_To, dbo.Article.Vote_yes, dbo.Article.Vote_no, 
      dbo.Article.ApproveOpinion, dbo.Article.ApproveUserID, dbo.Article.ApproveTime, 
      dbo.Users.Email, dbo.Users.NickName, dbo.ForumCategories.CategoryName, 
      Users_1.NickName AS ApproveUser
FROM dbo.Users Users_1 RIGHT OUTER JOIN
      dbo.Article ON Users_1.UserID = dbo.Article.ApproveUserID RIGHT OUTER JOIN
      dbo.Users INNER JOIN
      dbo.ForumPosts ON dbo.Users.UserID = dbo.ForumPosts.UserID LEFT OUTER JOIN
      dbo.ForumCategories ON 
      dbo.ForumPosts.CategoryID = dbo.ForumCategories.CategoryID ON 
      dbo.Article.PostID = dbo.ForumPosts.PostID
ORDER BY dbo.ForumPosts.CreateDate DESC

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE sp_ApproveArticle
/*
	Mult-purpose Article Search Capability	
	Version 0.6
	Coded By Zhang Xiaohua
	30/6/06
*/
(
	@PostID		integer,
	@CategoryID	integer,
	@Status		varchar(50),
	@ApproveUserID	integer,
	@ApproveOpinion	varchar(500)	
)
AS
    IF (not exists (Select * from Article where PostID = @PostID))
      	Begin
 	    Insert INTO Article (PostID, ApproveOpinion, ApproveUserID) Values (@PostID, @ApproveOpinion, @ApproveUserID);
	End
    ELSE
	Begin
		UPDATE Article SET ApproveOpinion=@ApproveOpinion, ApproveUserID=@ApproveUserID
	 	 WHERE PostID=@PostID;
	End

    UPDATE ForumPosts SET CategoryID=@CategoryID, Status=@Status
     WHERE PostID=@PostID;
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE sp_DeleteUserArticle 
/*
	Mult-purpose Article Search Capability	
	Version 0.6
	Coded By Zhang Xiaohua
	30/6/06
*/
(
	@PostID	integer
)
AS
	DELETE Article WHERE PostID=@PostID;
	DELETE ForumPosts WHERE PostID=@PostID;
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE sp_GetArticleByPostID
/*
	Mult-purpose Article Search Capability	
	Version 0.6
	Coded By Zhang Xiaohua
	30/6/06
*/
(
	@PostID 	int
)
 AS

	SELECT * FROM v_articles WHERE PostID=@PostID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE sp_GetArticlesByKey
/*
	Mult-purpose Article Search Capability	
	Version 0.6
	Coded By Zhang Xiaohua
	30/6/06
*/
(
	@Key		nvarchar(200)
)
 AS
SELECT * FROM v_articles WHERE Subject LIKE '%' + @Key +'%'
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE sp_GetArticlesByScripture 
/*
	Mult-purpose Article Search Capability	
	Version 0.6
	Coded By Zhang Xiaohua
	30/6/06
*/
(
	@Title		nvarchar(50),
	@Chapter	smallint,
	@Section	smallint,
	@Chapter_to	smallint,
	@Section_to 	smallint
)
 AS

	SELECT * FROM v_articles 
	 WHERE BibleTitle=@Title
	   AND NOT ((@Chapter_to=BibleChapter AND @Section_to<BibleSection) 
		    OR (@Chapter_to<BibleChapter))
	   AND NOT ((@Chapter=BibleChapter_To AND @Section>BibleSection_To) 
		    OR (@Chapter>BibleChapter_To))
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE sp_GetArticlesByTopic
/*
	Mult-purpose Article Search Capability	
	Version 0.6
	Coded By Zhang Xiaohua
	30/6/06
*/
(
	@Topic		nvarchar(50),
	@SubTopic	nvarchar(50)
)
 AS
	SELECT * FROM v_articles WHERE Topic=@Topic AND SubTopic=@SubTopic
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE sp_GetUserArticles
/*
	Mult-purpose Article Search Capability	
	Version 0.6
	Coded By Zhang Xiaohua
	30/6/06
*/
(
	@UserID	integer
)
 AS
	SELECT * FROM v_articles WHERE Userid=@UserID;
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

 CREATE PROCEDURE [sp_InsertArticle]
/*
	Mult-purpose Article Search Capability	
	Version 0.6
	Coded By Zhang Xiaohua
	30/6/06
*/
(
	@UserID		integer,
	@Subject		nvarchar(200),
	@Body			ntext,
	@Title			nvarchar(50),
	@Chapter		smallint,
	@Section		smallint,
	@Chapter_to		smallint,
	@Section_to		smallint,
	@Topic			nvarchar(50),
	@SubTopic		nvarchar(50) 
)
AS 
	DECLARE @CategoryID integer, @CreateDate datetime, @NumberOfVisits smallint, @Status varchar(50), @Type varchar(10);
	SET @CategoryID=44;			
	SET @CreateDate=getdate();
	SET @NumberOfVisits=1;
	SET @Type='blog';
	SET @Status='Pending';

	SET NOCOUNT OFF; 
	INSERT INTO ForumPosts (CategoryID, Subject, Body, UserID, CreateDate, NumberOfVisits, Status, Type)
		values (	@CategoryID, @Subject, @Body, @UserID, @CreateDate, @NumberOfVisits, @Status, @Type);

	DECLARE @PostID INTEGER;
	SET @PostID=@@IDENTITY;

	INSERT INTO Article (PostID, Topic, SubTopic, BibleTitle, BibleChapter, BibleSection, BibleChapter_To, BibleSection_To)
	values (
		@PostID, @Topic, @SubTopic, @Title, @Chapter, @Section, @Chapter_to, @Section_to);
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE sp_VoteArticle
/*
	Mult-purpose Article Search Capability	
	Version 0.6
	Coded By Zhang Xiaohua
	30/6/06
*/
(
	@PostID	integer,
	@Flag		varchar(1)	
)
AS
    IF (not exists (Select * from Article where PostID = @PostID))
      	Begin
         	IF @Flag='1' 
			Insert INTO Article (PostID, Vote_yes) Values (@PostID, 1);
	 	ELSE
			Insert INTO Article (PostID, Vote_no) Values (@PostID, 1);
      	End
    ELSE
	Begin
		IF @Flag='1' 
			UPDATE Article SET Vote_yes=ISNULL(Vote_yes,0)+1
		 	 WHERE PostID=@PostID;
		ELSE
			UPDATE Article SET Vote_no=ISNULL(Vote_no,0)+1
		 	 WHERE PostID=@PostID;
	End
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

